﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DBManager.News
{
    public class NewsDAL
    {
        public static DataTable CountGetListNewsByZone(string ZoneID)
        {
            string sql = @"
			SELECT count(*) as TongBanGhi
			FROM News 
			WHERE Status=1 and ZoneId = " + comm.to_sqlnumber(ZoneID) + " or ZoneIdOther like N'%" + ZoneID + @",%' 
			and DistributionDate <= GETDATE() ";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetListNewsByZone(string PageSize, string PageIndex, string ZoneID)
        {
            int IndexSize =int.Parse(PageSize) * int.Parse(PageIndex);
            string sql = @"SELECT  * 
		    FROM 
		    ( 
			SELECT TOP(" + IndexSize + @") 
				id,
				ZoneId,
				ZoneIdOther,
				Title,
				Sapo,
				Avatar,
				DistributionDate,
				[Url],	
				Author,
				ROW_NUMBER() OVER (ORDER BY ID DESC ) Row		 
			FROM News 
            
			WHERE Status=1 and (ZoneId = " + comm.to_sqlnumber(ZoneID) + " or ZoneIdOther ='" + ZoneID + @",' )  
            and id not in(5,6)
			and DistributionDate <= GETDATE() 
		) News 
		WHERE   ROW Between (" + comm.to_sqlnumber(PageIndex) + " -1)*" + comm.to_sqlnumber(PageSize) + " + 1 AND " + IndexSize + "";
            return DBUtil.SelectTable(sql);
        }

        public static DataTable GetListNews(string PageSize, string PageIndex)
        {
            int IndexSize = int.Parse(PageSize) * int.Parse(PageIndex);
            string sql = @"SELECT  * 
		    FROM 
		    ( 
			SELECT TOP(" + IndexSize + @") 
				id,
				ZoneId,
				ZoneIdOther,
				Title,
				Sapo,
				Avatar,
				DistributionDate,
				[Url],	
				Author,
				ROW_NUMBER() OVER (ORDER BY ID DESC ) Row		 
			FROM News 
			WHERE Status=1  
			and DistributionDate <= GETDATE() 
		) News 
		WHERE   ROW Between (" + comm.to_sqlnumber(PageIndex) + " -1)*" + comm.to_sqlnumber(PageSize) + " + 1 AND " + IndexSize + "";
            return DBUtil.SelectTable(sql);
        }

        public static DataTable GetNewsFocus(string Top)
        {
            string sql = @"
			SELECT TOP(" + Top + @") 
				id,
				ZoneId,
				ZoneIdOther,
				Title,
				Sapo,
				Avatar,
				DistributionDate,
				[Url],	
				Author 
			FROM News 
			WHERE Status=1 and IsFocus=1 order by id desc";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetDetailNews(string ID)
        {
            string sql = @"SELECT * FROM News 
			WHERE ID="+ID;
            return DBUtil.SelectTable(sql);
        }
    }
}
